/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package christieprogram;

import java.awt.HeadlessException;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import jxl.*;
import jxl.read.biff.BiffException;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.apache.poi.xssf.usermodel.*;

/**
 *
 * @author Alex
 */
public class ChristieProgram {

    private final static int TwoSplitsExpected = 2;
    private final static String[] choices = {"Yes", "No", "Ignore", "Ignore All"};
    public static File[] file;
    public static int num_files;
    public static BufferedReader input;
    static int Title = 0;
    static int First = 1;
    static int Middle = 2;
    static int Last = 3;
    static int Suffix = 4;
    static int Street = 5;
    static int City = 6;
    static int State = 7;
    static int Zip = 8;
    static int Company = 9;
    static int Name = 10;
    static String[] column_name = {"Title", "First", "Middle", "Last", "Suffix", "Street", "City", "State", "Zip", "Company", "Name"};
    static int num_col = 10;
    static Hashtable<String, String> road_equiv;

    static int file_extension(String name, String ex) {
        if (name.length() >= 4 && name.substring(name.length() - 4).compareToIgnoreCase(ex) == 0) {
            return 0;
        } else if (name.length() >= 4 && name.charAt(name.length() - 4) == '.') {
            JOptionPane.showMessageDialog(null, "\nThis file: \"" + name + "\" is not in the proper format. Please change the format to a " + ex + " file.");
            return -1;
        }
        return 1;
    }

    static void merge_files(File file1, File file2) {
        try (FileReader fr = new FileReader(file1)) {
            BufferedReader br = new BufferedReader(fr);
            String[][] merged = new String[100000][];
            int index = 1;
            int num_duplicates = 0;
            boolean end_of_file;
            String[] line;
            String sline;
            String in;
            String[][] equiv_names = load_names("equiv_names.data");
            String[][] diff_names = load_names("diff_names.data");
            String[][] new_equiv = new String[1000][2];
            int eindex = 0;
            String[][] new_diff = new String[1000][2];
            int dindex = 0;
            int result, result2;
            boolean ignore_first_names = false;
            String[] header = null;
            for (int files = 0; files < 2; files++) {
                sline = br.readLine();
                header = sline.split(","); // will need to add more columns later
                end_of_file = false;
                while (true) {
                    sline = br.readLine();
                    if (sline == null) {
                        break;
                    } else {
                        line = sline.split(",");
                        for (int i = 0; i < index; i++) {
                            result = are_duplicates(line, merged[i], equiv_names, diff_names);
                            result2 = are_duplicates(line, merged[i], new_equiv, new_diff);
                            if (result == 0 && result2 == 0) {
                                while (true) {
                                    if (ignore_first_names) {
                                        result = -1;
                                        break;
                                    }

                                    int response = JOptionPane.showOptionDialog(null, "Are these names equivilent? (y/n) or type i to ignore all.\n" + line[First] + " : " + merged[i][First],
                                            "NamesEquivalent?", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, choices, "Ignore");
                                    if (response == Choices.YES) {
                                        new_equiv[eindex][0] = line[First];
                                        new_equiv[eindex][1] = merged[i][First];
                                        eindex++;
                                        result = 1;
                                        break;
                                    }
                                    if (response == Choices.NO) {
                                        new_diff[dindex][0] = line[First];
                                        new_diff[dindex][1] = merged[i][First];
                                        dindex++;
                                        result = -1;
                                        break;
                                    }
                                    if (response == Choices.IGNORE_ALL) {
                                        ignore_first_names = true;
                                        result = -1;
                                        break;
                                    }
                                }
                            }
                            if (result == 1 || result2 == 1) {
                                num_duplicates++;
                                for (int j = 0; j < num_col; j++) {
                                    if (line[j].length() > merged[i][j].length()) {
                                        merged[i][j] = line[j];
                                    }
                                }
                                break;
                            }
                            if (i == index - 1) {
                                merged[index] = line;
                                index++;
                            }
                        }
                    }
                }
                br.close();
                fr.close();
                FileReader gr = new FileReader(file2);
                br = new BufferedReader(fr);
            }
            update_names("equiv_names.data", equiv_names, new_equiv, eindex);
            update_names("diff_names.data", diff_names, new_diff, dindex);
            String timeStamp = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(Calendar.getInstance().getTime());
            create_file("Merged-" + timeStamp + ".xls", header, merged, index);
            JOptionPane.showMessageDialog(null, "Successfull merging of the following files:\n" + print_file_name(file1)
                    + " \n" + print_file_name(file2) + "\n" + num_duplicates + " duplicates were found and removed."
                    + "\nThe following file has been created: " + print_file_name(file1));
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "There was an error: line 165\n" + e);
        }
    }

    static String print_file_name(File fileMod) {
        return ("File #" + ": " + fileMod.getName());
    }
//    static void print_file_list() {
//        for (int i = 0; i < num_files; i++) {
//            JOptionPane.showMessageDialog(null, "File #" + (i + 1) + ": " + file[i].getName());
//        }
//    }

    static int are_duplicates(String[] line1, String[] line2, String[][] equiv_names, String[][] diff_names) {
        for (int i = Title; i <= Suffix; i++) {
            if (line1[i].compareTo("") != 0) {
                break;
            }
            if (i == Suffix) {
                if (line1.length > Company && line2.length > Company && line1[Company].compareTo("") != 0 && line1[Company].compareToIgnoreCase(line2[Company]) == 0) {
                    return 1;
                } else {
                    return -1;
                }
            }
        }
        for (int i = Title; i <= Suffix; i++) {
            if (line2[i].compareTo("") != 0) {
                break;
            }
            if (i == Suffix) {
                if (line1.length > Company && line2.length > Company && line2[Company].compareTo("") != 0 && line1[Company].compareToIgnoreCase(line2[Company]) == 0) {
                    return 1;
                } else {
                    return -1;
                }
            }
        }
        for (int check = Title; check <= Zip; check++) {
            if (check == First || check == Middle) {
                continue;
            }
            if (line1[check].compareTo(line2[check]) == 0
                    || line1[check].compareTo("") == 0
                    || line2[check].compareTo("") == 0) {
            } else {
                return -1;
            }
        }
        if (line1[Middle].compareTo(line2[Middle]) == 0
                || line1[Middle].compareTo("") == 0
                || line2[Middle].compareTo("") == 0
                || (line1[Middle].length() == 2 && line1[Middle].charAt(1) == '.' && line1[Middle].compareTo(line2[Middle].substring(0, 1)) == 0)
                || (line2[Middle].length() == 2 && line2[Middle].charAt(1) == '.' && line2[Middle].compareTo(line1[Middle].substring(0, 1)) == 0)) {
        } else {
            return -1;
        }
        if (line1[First].compareTo(line2[First]) == 0
                || (line2[First].length() > 0 && line1[First].length() == 2 && line1[First].charAt(1) == '.' && line1[First].compareTo(line2[First].substring(0, 1)) == 0)
                || (line1[First].length() > 0 && line2[First].length() == 2 && line2[First].charAt(1) == '.' && line2[First].compareTo(line1[First].substring(0, 1)) == 0)) {
            return 1;
        } else {
            for (int i = 0; i < equiv_names.length; i++) {
                if (line1[First].compareTo(equiv_names[i][0]) == 0 && line2[First].compareTo(equiv_names[i][1]) == 0) {
                    return 1;
                }
                if (line1[First].compareTo(equiv_names[i][1]) == 0 && line2[First].compareTo(equiv_names[i][0]) == 0) {
                    return 1;
                }
            }
            for (int i = 0; i < diff_names.length; i++) {
                if (line1[First].compareTo(diff_names[i][0]) == 0 && line2[First].compareTo(diff_names[i][1]) == 0) {
                    return -1;
                }
                if (line1[First].compareTo(diff_names[i][1]) == 0 && line2[First].compareTo(diff_names[i][0]) == 0) {
                    return -1;
                }
            }
            return 0;
        }
    }

    static String[][] load_names(String filename) {
        String read;
        try {
            File list = new File(filename);
            if (!list.exists()) {
                return new String[0][];
            }
            BufferedReader br = new BufferedReader(new FileReader(list));
            int length = Integer.parseInt(br.readLine().split(",")[0]);// todo get rid of
            Pair<String, String> temp;
            ArrayList<Pair<String, String>> names = new ArrayList<>();
            while ((read = br.readLine()) != null) {
                if ((temp = LineSplitter(read, ',')) != null) {
                    names.add(LineSplitter(read, ','));
                }
            }
            return pairlistConverter(names);
        } catch (IOException | NumberFormatException | HeadlessException e) {
            JOptionPane.showMessageDialog(null, "There was an error: 283\n" + e);
            return null;
        }
    }

    public static String[][] pairlistConverter(ArrayList<Pair<String, String>> pairs) {
        String[][] strings = new String[pairs.size()][2];
        for (int i = 0; i < pairs.size(); i++) {
            strings[i][0] = pairs.get(i).getKey();
            strings[i][1] = pairs.get(i).getValue();
        }
        return strings;
    }

    static void update_names(String filename, String[][] names, String[][] new_names, int index) {
        try {
            BufferedWriter bw = new BufferedWriter(new FileWriter(new File(filename)));
            bw.write("" + (names.length + index) + ",");
            bw.newLine();
            for (int i = 0; i < names.length; i++) {
                bw.write("" + names[i][0] + "," + names[i][1]);
                bw.newLine();
            }
            for (int i = 0; i < index; i++) {
                bw.write("" + new_names[i][0] + "," + new_names[i][1]);
                bw.newLine();
            }
            bw.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "There was an error:Line-284\n" + e);
        }
    }

    static XSSFSheet create_file(String file_name, String[] header, String[][] data, int index) throws Exception {
        XSSFWorkbook workbook = NewWorkBook(new File(file_name + ".xlsx"));
        XSSFSheet excelSheet = NewWorksheet(workbook, "null");
        try {
            excelSheet.createRow(0);
            for (int Col = 0; Col < header.length; Col++) {
                excelSheet.getRow(0).createCell(Col).setCellValue(new XSSFRichTextString(header[Col]));
            }
            for (int Row = 0; Row < data.length; Row++) {
                if (data[Row] != null && !empty(data[Row])) {
                    excelSheet.createRow(excelSheet.getLastRowNum() + 1);
                    for (int Col = 0; Col < data[Row].length; Col++) {
                        cellSetter(excelSheet.getRow(excelSheet.getLastRowNum()).createCell(Col), data[Row][Col]);
                    }
                }
            }
            workbook.write(new FileOutputStream(file_name + ".xlsx", false));
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "There was an error: Line-331\n" + e);
        }


        return excelSheet;
    }

    static boolean is_int(String s) {
        try {
            Integer.parseInt(s);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "\"" + s + "\" is not an integer.");
            return false;
        }
        return true;
    }

    static boolean is_double(String s) {
        try {
            Double.parseDouble(s.replaceAll("\\$", "").replaceAll(",", ""));
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    static void format_file(File fileMod, boolean duplicateCheck) throws Exception {
        //try
        {
//            if (file_num >= num_files) {
//                JOptionPane.showMessageDialog(null,"" + (file_num + 1) + " is not a valid file number.");
//                return;
//            }
            BufferedReader br = new BufferedReader(new FileReader(fileMod));
            String[] header = (br.readLine() + " ").split(",");
            header[header.length - 1] = header[header.length - 1].substring(0, header[header.length - 1].length() - 1);
            int[] file_col = new int[100];
            int num_fc = 0;
            int[] index_map = new int[num_col + 1];
            for (int i = 0; i < num_col + 1; i++) {
                index_map[i] = -1;
            }
            for (int i = 0; i < header.length; i++) {
                if (header[i].compareTo("") == 0) {
                    continue;
                }
                for (int j = 0; j <= num_col; j++) {
                    if (column_name[j].compareToIgnoreCase(header[i]) == 0) {
                        index_map[j] = i;
                        break;
                    }
                    if (j == num_col) {
                        file_col[num_fc] = i;
                        num_fc++;
                    }
                }
            }
            String[] new_header = new String[num_col + num_fc];
            for (int i = 0; i < num_col; i++) {
                new_header[i] = column_name[i];
            }
            for (int i = 0; i < num_fc; i++) {
                new_header[num_col + i] = header[file_col[i]];
            }
            if (index_map[num_col] == -1 && (index_map[First] == -1 || index_map[Last] == -1)) {
                JOptionPane.showMessageDialog(null, "Error in file:");
                print_file_name(fileMod);
                JOptionPane.showMessageDialog(null, "File must have either a \"Name\" column or both a \"First\" and a \"Last\" column. Formatting failed.\n\n");
                return;
            }
            String[] poss_surnames = {"Mr", "Mrs", "Ms", "Dr", "Rev"};
            String[] poss_suffixes = {"I", "II", "III", "IV", "PA", "MD", "CPCU", "CPA", "Jr", "Sr", "Esq", "PhD"};
            String[][] surnames = load_names("surnames.data");
            String[][] new_surnames = new String[1000][2];
            int sindex = 0;
            String test;
            String[] line;
            String[] modified = new String[num_col + num_fc];
            String[][] new_file = new String[100000][];
            int index = 0;
            String[] fl_name;
            boolean ignore_surnames = false;


            String[] caps = {"Us", "Pac", "Pc", "Llc", "L.l.c.", "Nj", "Ne", "Se", "Sw", "Nw", "Usa"};
            String mod_street;
            int result;
            String in;
            String name;
            String[] line_comas;
            String[] temp;
            String[][] equiv_names = load_names("equiv_names.data");
            String[][] diff_names = load_names("diff_names.data");
            String[] duplicates = new String[100000];
            int dindex = 0;
            String[] ms;
            while (true) {
                test = br.readLine();
                if (test == null) {
                    break;
                }
                line = read_line(header.length, test);
                if (line == null) {
                    JOptionPane.showMessageDialog(null, "The line number is " + (index + 2) + ".");
                    continue;
                }
                for (int i = 0; i < line.length; i++) {
                    if (line[i].replaceAll(" ", "").compareTo("") != 0) {
                        break;
                    }
                    if (i == line.length - 1) {
                        line = null;
                        break;
                    }
                }
                if (line == null) {
                    continue;
                }
                for (int i = 0; i < num_col + 1; i++) {
                    if (index_map[i] != -1) {
                        line[index_map[i]] = proper_case(line[index_map[i]], false);
                    }
                }
                for (int i = Street; i <= Company; i++) {
                    if (index_map[i] == -1) {
                        continue;
                    }
                    for (int j = 0; j < caps.length; j++) {
                        if (line[index_map[i]].contains(" " + caps[j] + " ")) {
                            line[index_map[i]] = line[index_map[i]].replaceAll(" " + caps[j] + " ", " " + caps[j].toUpperCase() + " ");
                        }
                        if (line[index_map[i]].contains(" " + caps[j] + ",")) {
                            line[index_map[i]] = line[index_map[i]].replaceAll(" " + caps[j] + ",", " " + caps[j].toUpperCase() + ",");
                        }
                        if (line[index_map[i]].length() >= caps[j].length() && line[index_map[i]].substring(line[index_map[i]].length() - caps[j].length()).compareTo(caps[j]) == 0) {
                            line[index_map[i]] = line[index_map[i]].substring(0, line[index_map[i]].length() - caps[j].length()) + caps[j].toUpperCase();
                        }
                        if (line[index_map[i]].length() > caps[j].length() && line[index_map[i]].substring(0, caps[j].length() + 1).compareTo(caps[j] + " ") == 0) {
                            line[index_map[i]] = caps[j].toUpperCase() + line[index_map[i]].substring(caps[j].length(), line[index_map[i]].length());
                        }
                    }
                }
                modified = new String[num_col + num_fc];
                for (int i = 0; i < num_col; i++) {
                    modified[i] = "";
                }
                // first, merge name columns into one string
                if (index_map[Name] != -1) {
                    name = line[index_map[Name]];
                } else {
                    name = "";
                    for (int i = Title; i <= Suffix; i++) {
                        if (index_map[i] != -1 && line[index_map[i]].compareTo("") != 0) {
                            if (name.compareTo("") != 0) {
                                name = name + " ";
                            }
                            name = name + line[index_map[i]];
                        }
                    }
                }
                name = name.replaceAll(",", "");
                //Break apart a Name column into the modified string array
                if (name.trim().compareTo("") == 0) {
                    modified[Title] = "";
                    modified[First] = "";
                    modified[Last] = "";
                    modified[Suffix] = "";
                } else {
                    name = name.replaceAll("\\.", "");
                    name = name.replaceAll("Reverend", "Rev");
                    name = name.replaceAll("Revd", "Rev");
                    name = name.replaceAll("Rev'd", "Rev");
                    int tl;
                    do {
                        tl = name.length();
                        name = name.replaceAll("  ", " ");
                    } while (tl != name.length());
                    fl_name = name.split(" ");
                    for (int i = 0; i < poss_surnames.length; i++) {
                        for (int j = 0; j < fl_name.length; j++) {
                            if (fl_name[j].compareToIgnoreCase(poss_surnames[i]) == 0) {
                                if (modified[Title].length() != 0) {
                                    modified[Title] += " ";
                                }
                                modified[Title] += poss_surnames[i] + ".";
                                fl_name[j] = "";
                            }
                        }
                        name = "";
                        for (int j = 0; j < fl_name.length; j++) {
                            if (fl_name[j].length() != 0) {
                                name += fl_name[j] + " ";
                            }
                        }
                        fl_name = name.split(" ");
                    }
                    for (int i = 0; i < poss_suffixes.length; i++) {
                        for (int j = 0; j < fl_name.length; j++) {
                            if (fl_name[j].compareToIgnoreCase(poss_suffixes[i]) == 0) {
                                if (modified[Suffix].length() != 0) {
                                    modified[Suffix] += " ";
                                }
                                if (i > 7) {
                                    modified[Suffix] += poss_suffixes[i] + ".";
                                } else {
                                    modified[Suffix] += poss_suffixes[i];
                                }
                                fl_name[j] = "";
                            }
                        }
                        name = "";
                        for (int j = 0; j < fl_name.length; j++) {
                            if (fl_name[j].length() != 0) {
                                name += fl_name[j] + " ";
                            }
                        }
                        fl_name = name.split(" ");
                    }
                    modified[Suffix] = modified[Suffix].replaceAll("PhD\\.", "Ph.D.");
                    modified[Suffix] = modified[Suffix].replaceAll("CPCU\\.", "CPCU");
                    modified[Suffix] = modified[Suffix].replaceAll("CPA\\.", "CPA");
                    modified[Suffix] = modified[Suffix].replaceAll("PA\\.", "PA");
                    modified[Suffix] = modified[Suffix].replaceAll("MD\\.", "M.D.");
                    if (fl_name.length == 1) {
                        modified[Last] = fl_name[0];
                    } else if (fl_name.length == 2) {
                        modified[First] = fl_name[0];
                        modified[Last] = fl_name[1];
                    } else if (fl_name.length == 3) {
                        modified[First] = fl_name[0];
                        modified[Middle] = fl_name[1];
                        modified[Last] = fl_name[2];
                    } else if (fl_name.length == 4) {
                        if (fl_name[1].length() == 1) {
                            modified[First] = fl_name[0];
                            if (fl_name[2].length() == 1) {
                                modified[Middle] = "" + fl_name[1] + " " + fl_name[2];
                            } else {
                                modified[Middle] = fl_name[1];
                            }
                            modified[Last] = fl_name[3];
                        } else if (fl_name[2].length() == 1) {
                            modified[First] = "" + fl_name[0] + fl_name[1];
                            modified[Middle] = fl_name[2];
                            modified[Last] = fl_name[3];
                        } else {
                            if (index_map[First] != -1 && index_map[Last] != -1) {
                                for (int i = First; i <= Suffix; i++) {
                                    if (index_map[i] != -1) {
                                        modified[i] = line[index_map[i]].replaceAll("\\.", "");
                                    }
                                }
                            } else {
                                modified[First] = fl_name[0];
                                modified[Middle] = fl_name[1] + " " + fl_name[2];
                                modified[Last] = fl_name[3];
                            }
                        }
                    } else {
                        //JOptionPane.showMessageDialog(null,"Name format not recognized in line "+(index+2)+". The program will not format the name in this line.");
                        //JOptionPane.showMessageDialog(null,test+"\n");
                        if (index_map[First] != -1 && index_map[Last] != -1) {
                            for (int i = First; i <= Suffix; i++) {
                                if (index_map[i] != -1) {
                                    modified[i] = line[index_map[i]].replaceAll("\\.", "");
                                }
                            }
                        }
                    }
                }
                if (modified[First].length() == 1 && modified[First].charAt(0) != '&') {
                    modified[First] += ".";
                }
                if (modified[Middle].length() == 1 && modified[Middle].charAt(0) != '&') {
                    modified[Middle] += ".";
                }
                modified[Last] = proper_case(modified[Last], true);
                // at this point, the modified string array should have correct values in the first four fields
                // now fix the titles
                boolean done = false;
                if (modified[Title].compareTo("") == 0) {
                    for (int i = 0; i < surnames.length; i++) {
                        if (modified[First].compareTo(surnames[i][0]) == 0) {
                            modified[Title] = surnames[i][1];
                            done = true;
                            break;
                        }
                    }
                    if (!done) {
                        for (int i = 0; i < sindex; i++) {
                            if (modified[First].compareTo(new_surnames[i][0]) == 0) {
                                modified[Title] = new_surnames[i][1];
                                done = true;
                                break;
                            }
                        }
                    }
                    if (!done && modified[Title].compareTo("") == 0 && modified[First].compareTo("") != 0) {
                        for (int i = 0; i < sindex; i++) {
                            if (modified[First].compareTo(new_surnames[i][0]) == 0) {
                                modified[Title] = new_surnames[i][1];
                            }
                        }
                        while (true) {
                            if (ignore_surnames) {
                                break;
                            }
                            String named;
                            if (modified[Middle].length() > 0) {
                                named = (modified[First] + " " + modified[Middle] + " " + modified[Last]);
                            } else {
                                named = (modified[First] + " " + modified[Last]);
                            }
                            String[] genderChoices = {"Male", "Female", "Ignore All", "Ignore Once"};
                            int response = JOptionPane.showOptionDialog(null, "Is the following name male, female or deserves no title? .\n" + named,
                                    "Gender?", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, genderChoices, "Ignore");
                            if (response == Choices.MALE) {
                                new_surnames[sindex][0] = modified[First];
                                new_surnames[sindex][1] = "Mr.";
                                modified[Title] = "Mr.";
                                sindex++;
                                break;
                            }
                            if (response == Choices.FEMALE) {
                                new_surnames[sindex][0] = modified[First];
                                new_surnames[sindex][1] = "Ms.";
                                modified[Title] = "Ms.";
                                sindex++;
                                break;
                            }
                            if (response == Choices.IGNORE) {
                                new_surnames[sindex][0] = modified[First];
                                new_surnames[sindex][1] = "";
                                sindex++;
                                break;
                            }
                            if (response == Choices.IGNORE_ALL) {
                                ignore_surnames = true;
                            }
                        }
                    }
                } else if (modified[Title].charAt(modified[Title].length() - 1) != '.') {
                    modified[Title] = "" + modified[Title] + ".";
                }


                // now the surnames have a period and have all been entered.
                // fix the street addresses
                if (index_map[Street] != -1) {
                    mod_street = StreetAbreviatorFix(line[index_map[Street]]);

                    mod_street = mod_street.replaceAll("(?i)PO Box", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\.O\\. Box", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\. O\\. Box", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P O Box", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)PO Box#", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\.O\\. Box#", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\. O\\. Box#", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P O Box#", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)PO Box #", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\.O\\. Box #", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P\\. O\\. Box #", "PO Box");
                    mod_street = mod_street.replaceAll("(?i)P O Box #", "PO Box");
                    mod_street = mod_street.replaceAll(" No ", " #");
                    mod_street = mod_street.replaceAll(" No\\.", " #");
                    mod_street = mod_street.replaceAll(", #", " #");
                    mod_street = mod_street.replaceAll(" #", ", #");
                    mod_street = mod_street.replaceAll(", PO Box", " PO Box");
                    mod_street = mod_street.replaceAll(" PO Box", ", PO Box");
                    mod_street = mod_street.replaceAll("Apt ", "Apt");
                    mod_street = mod_street.replaceAll("Apt\\. ", "Apt");
                    mod_street = mod_street.replaceAll(", Apt", " Apt");
                    mod_street = mod_street.replaceAll(" Apt\\.", " Apt");
                    mod_street = mod_street.replaceAll(" Apt", ", Apt. ");
                    mod_street = mod_street.replaceAll(", Unit", " Unit");
                    mod_street = mod_street.replaceAll(" Unit", ", Unit");
                    mod_street = mod_street.replaceAll(", Suite", " Suite");
                    mod_street = mod_street.replaceAll(" Suite", ", Suite");
                    mod_street = mod_street.replaceAll(", Building", " Building");
                    mod_street = mod_street.replaceAll(" Building", ", Building");
                    mod_street = mod_street.replaceAll(" - ", ", ");
                    ms = mod_street.split(" ");
                    for (int i = 0; i < ms.length; i++) {
                        if (ms[i].length() >= 2 && (ms[i].substring(ms[i].length() - 2).compareTo("st") == 0
                                || ms[i].substring(ms[i].length() - 2).compareTo("nd") == 0
                                || ms[i].substring(ms[i].length() - 2).compareTo("rd") == 0
                                || ms[i].substring(ms[i].length() - 2).compareTo("th") == 0)) {
                            continue;
                        }
                        if (ms[i].length() > 0 && is_numeric(ms[i].charAt(0))) {
                            ms[i] = ms[i].toUpperCase();
                        }
                    }
                    for (int i = 0; i < ms.length; i++) {
                        modified[Street] += ms[i];
                        if (i != ms.length - 1) {
                            modified[Street] += " ";
                        }
                    }
                }
                modified[City] = line[index_map[City]].replaceAll("Mt ", "Mount ").replaceAll("Pt ", "Point ");
                modified[State] = line[index_map[State]].toUpperCase();
                modified[Zip] = line[index_map[Zip]];
                // now the address is completely correct
                if (index_map[Company] != -1) {
                    modified[Company] = line[index_map[Company]];
                } else {
                    modified[Company] = "";
                }
                modified[Company] = modified[Company].replaceAll(" Inc\\.", " Inc");
                modified[Company] = modified[Company].replaceAll(", Inc", " Inc");
                modified[Company] = modified[Company].replaceAll(" Inc", ", Inc.");
                // fill in the data from the unused columns
                for (int i = 0; i < num_fc; i++) {
                    modified[num_col + i] = line[file_col[i]];
                }
                if (duplicateCheck) {
                    // now check if the new line is a duplicate and add the line
                    if (index == 0) {
                        new_file[index] = modified;
                        index++;
                    } else {
                        for (int i = 0; i < index; i++) {
                            result = are_duplicates(modified, new_file[i], equiv_names, diff_names);
                            if (result == 1) {
                                duplicates[dindex] = "";
                                for (int j = 0; j < num_col; j++) {
                                    duplicates[dindex] += modified[j] + " ";
                                }
                                dindex++;
                                duplicates[dindex] = "";
                                for (int j = 0; j < num_col; j++) {
                                    duplicates[dindex] += new_file[i][j] + " ";
                                }
                                dindex++;
                                for (int j = 0; j < num_col; j++) {
                                    if (modified[j].length() > new_file[i][j].length()) {
                                        new_file[i][j] = modified[j];
                                    }
                                }
                                for (int j = 0; j < num_fc; j++) {
                                    if (modified[file_col[j]].length() > new_file[i][file_col[j]].length()) {
                                        new_file[i][file_col[j]] = modified[file_col[j]];
                                    }
                                }
                                break;
                            }
                            if (i == index - 1) {
                                new_file[index] = modified;
                                index++;
                                break;
                            }
                        }
                    }
                } else {
                    new_file[index] = modified;
                    index++;
                }
            }
            if (modified.length != num_col + num_fc) {
                JOptionPane.showMessageDialog(null, test);
            }
            update_names("surnames.data", surnames, new_surnames, sindex);
            String timeStamp = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(Calendar.getInstance().getTime());
            create_file("Formatted-" + timeStamp + ".csv", new_header, new_file, index);
            JOptionPane.showMessageDialog(null, "The following file: " + print_file_name(fileMod)
                    + " has been successfully formatted. The new formatted file: " + print_file_name(fileMod)
                    + " was successfully created.");
            if (dindex == 0) {
                return;
            }
            int reply = JOptionPane.showConfirmDialog(null, "" + dindex / 2 + " duplicates were found and removed. \nWould you like to see the changes?");
            if (reply == JOptionPane.YES_OPTION) {
                for (int i = 0; i < dindex; i++) {
                    JOptionPane.showMessageDialog(null, duplicates[i]);
                }
            }
        }
        //catch (Exception e)
        {
            //JOptionPane.showMessageDialog(null,"There was an error:\n"+e);
        }
    }

    static String[] read_line(int length, String test) {
        String[] line;
        String[] line_comas = test.split("\"");
        if (line_comas.length == 1) {
            line = (test + " ").split(",");
            line[line.length - 1] = line[line.length - 1].substring(0, line[line.length - 1].length() - 1);
        } else if (line_comas.length == 2 || line_comas.length == 4) {
            JOptionPane.showMessageDialog(null, "Mismatched quote in the following line. Additional \" expected.\n" + test + "\nThe program will disregard the line.");
            return null;
        } else if (line_comas.length % 2 == 1) {
            int cindex = 0;
            line = new String[length];
            boolean blank = false;
            for (int i = 1; i < line_comas.length; i++) {
                if (line_comas[i].compareTo("") == 0) {
                    blank = true;
                    line = (test.replaceAll("\"", "") + " ").split(",");
                    line[line.length - 1] = line[line.length - 1].substring(0, line[line.length - 1].length() - 1);
                }
            }
            if (!blank) {
                for (int count = 0; count < line_comas.length / 2 + 1; count++) {
                    if (line_comas[2 * count].compareTo("") != 0) {
                        String[] temp;
                        if (count == 0) {
                            temp = (line_comas[0] + " ").split(",");
                        } else {
                            temp = (line_comas[2 * count].substring(1) + " ").split(",");
                        }
                        temp[temp.length - 1] = temp[temp.length - 1].substring(0, temp[temp.length - 1].length() - 1);
                        if (count == line_comas.length / 2) {
                            for (int i = 0; i < temp.length; i++) {
                                line[cindex] = temp[i];
                                cindex++;
                            }
                        } else {
                            for (int i = 0; i < temp.length - 1; i++) {
                                line[cindex] = temp[i];
                                cindex++;
                            }
                        }
                    }
                    if (count != line_comas.length / 2 && line_comas[2 * count + 1].compareTo("") != 0) {
                        line[cindex] = line_comas[2 * count + 1];
                        cindex++;
                    }
                }
            }
        } else {
            JOptionPane.showMessageDialog(null, "Bad Line:\n" + test);
            line = test.replaceAll("\"", "").split(",");
            JOptionPane.showMessageDialog(null, "The program will fix the line.");
        }
        return line;
    }

    static boolean is_numeric(char c) {
        if (c == '0' || c == '1' || c == '2' || c == '3' || c == '4' || c == '5' || c == '6' || c == '7' || c == '8' || c == '9' || c == '#') {
            return true;
        }
        return false;
    }

    static String proper_case(String text, boolean name) {
        String[] items;
        String c = "";
        String ans = text.toLowerCase();
        for (int count = 0; (name && count < 10) || (!name && count < 8); count++) {
            if (count == 0) {
                c = " ";
            } else if (count == 1) {
                c = "-";
            } else if (count == 2) {
                c = "`";
            } else if (count == 3) {
                c = "'";
            } else if (count == 4) {
                c = "&";
            } else if (count == 5) {
                c = "\\(";
            } else if (count == 6) {
                c = "\\.";
            } else if (count == 7) {
                c = "/";
            } else if (count == 8) {
                c = "Mc";
            } else if (count == 9) {
                c = "Di";
            }
            items = (ans + "x").split(c);
            if (items.length > 0) {
                items[items.length - 1] = items[items.length - 1].substring(0, items[items.length - 1].length() - 1);
            }
            ans = "";
            for (int i = 0; i < items.length; i++) {
                if (items[i].length() > 1) {
                    ans = ans + items[i].substring(0, 1).toUpperCase() + items[i].substring(1);
                } else {
                    ans = ans + items[i].toUpperCase();
                }
                if (i != items.length - 1) {
                    if (count == 2) {
                        ans = ans + "'";
                    } else if (count == 5) {
                        ans = ans + "(";
                    } else if (count == 6) {
                        ans = ans + ".";
                    } else {
                        ans = ans + c;
                    }
                }
            }
        }
        ans = ans.replaceAll("'S ", "'s ");
        if (ans.length() > 2 && ans.substring(ans.length() - 2).compareTo("'S") == 0) {
            ans = ans.substring(0, ans.length() - 2) + "'s";
        }
        return ans;
    }

    static void compile_finances(File fileMod) throws Exception {
        //try
        {
            BufferedReader br = new BufferedReader(new FileReader(fileMod));
            String[] header = (br.readLine() + " ").split(",");
            header[header.length - 1] = header[header.length - 1].substring(0, header[header.length - 1].length() - 1);
            boolean drb, lgb, g09b, lcb, agg2013;
            drb = false;
            lgb = false;
            g09b = false;
            lcb = false;
            agg2013 = false;
            for (int i = 0; i < header.length; i++) {
                if (header[i].compareToIgnoreCase("MostRecent") == 0) {
                    drb = true;
                }
                if (header[i].compareToIgnoreCase("LatestContribution") == 0) {
                    lgb = true;
                }
                if (header[i].compareToIgnoreCase("G09") == 0) {
                    g09b = true;
                }
                if (header[i].compareToIgnoreCase("LargestContribution") == 0) {
                    lcb = true;
                }
                if (header[i].compareToIgnoreCase("2013Aggregate") == 0) {
                    agg2013 = true;
                }
            }
            if (!drb) {
                JOptionPane.showMessageDialog(null, "Error in compile finances function. File must have a column labeled \"MostRecent\" for date received.");
            }
            if (!lgb) {
                JOptionPane.showMessageDialog(null, "Error in compile finances function. File must have a column labeled \"LatestContribution\".");
            }
            if (!g09b) {
                JOptionPane.showMessageDialog(null, "Error in compile finances function. File must have a column labeled \"G09\" for the general '09.");
            }
            if (!lcb) {
                JOptionPane.showMessageDialog(null, "Error in compile finances function. File must have a column labeled \"LargestContribution\".");
            }
            if (!agg2013) {
                JOptionPane.showMessageDialog(null, "Error in compile finances function. File must have a column labeled \"2013Aggregate\".");
            }
            if (!drb || !lgb || !g09b || !lcb || !agg2013) {
                return;
            }
            format_file(fileMod, false);
            JOptionPane.showMessageDialog(null, "Working on compiling the finances...");
            br = new BufferedReader(new FileReader(fileMod));
            header = (br.readLine() + " ").split(",");
            header[header.length - 1] = header[header.length - 1].substring(0, header[header.length - 1].length() - 1);
            String[][] equiv_names = load_names("equiv_names.data");
            String[][] diff_names = load_names("diff_names.data");
            String test;
            String[] line;
            String[][] new_file = new String[100000][header.length];
            int index = 0;
            int dr, lg, g09, lc, a13;
            dr = -1;
            lg = -1;
            g09 = -1;
            lc = -1;
            a13 = -1;
            for (int i = 0; i < header.length; i++) {
                if (header[i].compareToIgnoreCase("MostRecent") == 0) {
                    dr = i;
                }
                if (header[i].compareToIgnoreCase("LatestContribution") == 0) {
                    lg = i;
                }
                if (header[i].compareToIgnoreCase("G09") == 0) {
                    g09 = i;
                }
                if (header[i].compareToIgnoreCase("LargestContribution") == 0) {
                    lc = i;
                }
                if (header[i].compareToIgnoreCase("2013Aggregate") == 0) {
                    a13 = i;
                }
            }
            SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
            SimpleDateFormat formatter2 = new SimpleDateFormat("MM/dd/yy");
            SimpleDateFormat formatter3 = new SimpleDateFormat("yyyy");
            Date date1 = null, date2 = null;
            boolean id1, id2;
            String[] duplicates = new String[100000];
            int dindex = 0;
            int line_num = 2;
            while (true) {
                test = br.readLine();
                if (test == null) {
                    break;
                }
                line = read_line(header.length, test);
                if (line == null) {
                    System.out.print("Error in the following line. Line was ignored.\n" + test);
                    continue;
                }
                line[lg] = finance(line[lg]);
                line[g09] = finance(line[g09]);
                line[lc] = finance(line[lc]);
                line[a13] = finance(line[a13]);
                if (!is_double(line[lg]) || Double.parseDouble(line[lg].replaceAll("\\$", "").replaceAll(",", "")) == 0) {
                    if (is_double(line[g09]) && Double.parseDouble(line[g09].replaceAll("\\$", "").replaceAll(",", "")) != 0) {
                        line[lg] = finance(line[g09]);
                    } else if (is_double(line[lc]) && Double.parseDouble(line[lc].replaceAll("\\$", "").replaceAll(",", "")) != 0) {
                        line[lg] = finance(line[lc]);
                    } else if (is_double(line[a13]) && Double.parseDouble(line[a13].replaceAll("\\$", "").replaceAll(",", "")) != 0) {
                        line[lg] = finance(line[a13]);
                        line[a13] = "$0.00";
                    } else {
                        line[lg] = "$0.00";
                    }
                }
                if (!is_double(line[lc]) || Double.parseDouble(line[lc].replaceAll("\\$", "").replaceAll(",", "")) == 0) {
                    if (is_double(line[g09])) {
                        if (Double.parseDouble(line[lg].replaceAll("\\$", "").replaceAll(",", "")) > Double.parseDouble(line[g09].replaceAll("\\$", "").replaceAll(",", ""))) {
                            line[lc] = finance(line[lg]);
                        } else {
                            line[lc] = finance(line[g09]);
                        }
                    } else {
                        line[lc] = line[lg];
                    }
                }
                if (index == 0) {
                    new_file[0] = line;
                    index++;
                } else {
                    for (int i = 0; i < index; i++) {
                        if (are_duplicates(new_file[i], line, equiv_names, diff_names) == 1 && new_file[i][dr].compareTo(line[dr]) == 0) {
                            if (equiv_strings(new_file[i][lg], line[lg])) {
                                duplicates[dindex] = "(" + line_num + ") ";
                                for (int j = 0; j < line.length; j++) {
                                    duplicates[dindex] += line[j] + " ";
                                }
                                dindex++;
                                duplicates[dindex] = "(" + i + ") ";
                                for (int j = 0; j < new_file[i].length; j++) {
                                    duplicates[dindex] += new_file[i][j] + " ";
                                }
                                dindex++;
                                break;
                            }
                        }
                        if (i == index - 1) {
                            new_file[index] = line;
                            index++;
                            break;
                        }
                    }
                }
                line_num++;
            }
            String timeStamp = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(Calendar.getInstance().getTime());
            create_file("Duplicate_Finances_Removed-" + timeStamp + ".xls", header, new_file, index);
            br = new BufferedReader(new FileReader(fileMod));
            br.readLine();
            new_file = new String[100000][];
            index = 0;
            if (dindex != 0) {
                int response = JOptionPane.showConfirmDialog(null, "" + dindex / 2 + " exact duplicates were found and removed. Press YES to show these duplicates");
                if (response == JOptionPane.YES_OPTION) {
                    for (int i = 0; i < dindex; i++) {
                        JOptionPane.showMessageDialog(null, duplicates[i]);
                    }
                }
            }
            JOptionPane.showMessageDialog(null, "The following file was successfully created: "
                    + print_file_name(fileMod) + "\nWorking on compiling the finances...");
            duplicates = new String[100000];
            dindex = 0;
            while (true) {
                test = br.readLine();
                if (test == null) {
                    break;
                }
                line = read_line(header.length, test);
                if (line == null) {
                    continue;
                }
                if (line[dr].length() == 4 && can_parse(formatter3, line[dr])) {
                    date1 = formatter3.parse(line[dr]);
                } else if (line[dr].split("/").length != 3) {
                    date1 = null;
                } else if (line[dr].split("/")[2].length() == 4) {
                    date1 = formatter.parse(line[dr]);
                } else if (line[dr].split("/")[2].length() == 2) {
                    date1 = formatter2.parse(line[dr]);
                } else {
                    date1 = null;
                }
                if (date1 != null && date1.compareTo(formatter.parse("12/31/2011")) > 0) {
                    line[a13] = add(line[a13], line[lg]);
                }
                if (index == 0) {
                    new_file[index] = line;
                    index++;
                } else {
                    for (int i = 0; i < index; i++) {
                        if (are_duplicates(line, new_file[i], equiv_names, diff_names) == 1) {
                            duplicates[dindex] = "";
                            for (int j = 0; j < line.length; j++) {
                                duplicates[dindex] += line[j] + " ";
                            }
                            dindex++;
                            duplicates[dindex] = "";
                            for (int j = 0; j < new_file[i].length; j++) {
                                duplicates[dindex] += new_file[i][j] + " ";
                            }
                            dindex++;
                            new_file[i][a13] = add(new_file[i][a13], line[a13]);
                            if (line[dr].compareTo("") == 0) {
                                JOptionPane.showMessageDialog(null, "Line #" + (index + 2) + " with no \"Date Received\" was found and skipped:\n" + test);
                                continue;
                            }
                            if (new_file[i][dr].length() == 4 && can_parse(formatter3, new_file[i][dr])) {
                                date2 = formatter3.parse(new_file[i][dr]);
                            } else if (new_file[i][dr].split("/").length != 3) {
                                date2 = null;
                            } else if (new_file[i][dr].split("/")[2].length() == 4) {
                                date2 = formatter.parse(new_file[i][dr]);
                            } else if (new_file[i][dr].split("/")[2].length() == 2) {
                                date2 = formatter2.parse(new_file[i][dr]);
                            } else {
                                date2 = null;
                            }
                            id1 = is_double(line[lg]);
                            id2 = is_double(new_file[i][lg]);
                            // first, fix the latest contribution entry
                            if (date1 == null) {
                                dindex++;
                                continue;
                            }
                            if (date2 == null) {
                                new_file[i][dr] = line[dr];
                                new_file[i][lg] = finance(line[lg]);
                                new_file[i][g09] = finance(line[g09]);
                                new_file[i][a13] = finance(line[a13]);
                                dindex++;
                                continue;
                            }
                            if (date1.compareTo(date2) > 0) {
                                if (is_double(line[lg].replaceAll("\\$", "").replaceAll(",", ""))) {
                                    new_file[i][dr] = line[dr];
                                    new_file[i][lg] = finance(line[lg]);
                                } else {
                                    if (is_double(line[g09].replaceAll("\\$", "").replaceAll(",", ""))) {
                                        new_file[i][dr] = line[dr];
                                        new_file[i][lg] = finance(line[g09]);
                                    } else if (is_double(line[lc].replaceAll("\\$", "").replaceAll(",", ""))) {
                                        new_file[i][dr] = line[dr];
                                        new_file[i][lg] = finance(line[lc]);
                                    }
                                }
                            }
                            if (is_double(line[g09]) && !is_double(new_file[i][g09])) {
                                new_file[i][g09] = finance(line[g09]);
                            }
                            if (date1.compareTo(date2) == 0) {
                                if (!equiv_strings(new_file[i][lg], line[lg])) {
                                    new_file[i][lg] = add(new_file[i][lg], line[lg]);
                                }
                                if (is_double(new_file[i][lc]) && is_double(line[lg])) {
                                    if (Double.parseDouble(line[lg].replaceAll("\\$", "").replaceAll(",", "")) > Double.parseDouble(new_file[i][lc].replaceAll("\\$", "").replaceAll(",", ""))) {
                                        new_file[i][lc] = finance(line[lg]);
                                    }
                                }
                            }
                            if (is_double(line[lc]) && is_double(new_file[i][lc])) {
                                if (Double.parseDouble(line[lc].replaceAll("\\$", "").replaceAll(",", "")) > Double.parseDouble(new_file[i][lc].replaceAll("\\$", "").replaceAll(",", ""))) {
                                    new_file[i][lc] = finance(line[lc]);
                                }
                            }
                            duplicates[dindex] = "";
                            for (int j = 0; j < new_file[i].length; j++) {
                                duplicates[dindex] += new_file[i][j] + " ";
                            }
                            dindex++;
                            break;
                        }
                        if (i == index - 1) {
                            new_file[index] = line;
                            index++;
                            break;
                        }
                    }
                }
            }
            timeStamp = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(Calendar.getInstance().getTime());
            create_file("Finances-" + timeStamp, header, new_file, index);
            if (dindex != 0) {
                int response = JOptionPane.showConfirmDialog(null, "" + dindex / 3 + " duplicates were found and the financial data was compiled together. Press ok to show all duplicates");
                if (response == JOptionPane.YES_OPTION) {
                    for (int i = 0; i < dindex; i++) {
                        if (i % 3 == 2) {
                            JOptionPane.showMessageDialog(null, "The new compiled line is as follows:");
                        }
                        JOptionPane.showMessageDialog(null, duplicates[i]);
                        if (i % 3 == 2) {
                        }
                    }
                }
            }
            JOptionPane.showMessageDialog(null, "The following file: "
                    + print_file_name(fileMod) + " has up-to-date financial records. The new file: "
                    + print_file_name(fileMod) + " was successfully created.");
        }
        //catch (Exception e)
        {
            //	JOptionPane.showMessageDialog(null,"There was an error:\n"+e);
        }
    }

    static boolean can_parse(SimpleDateFormat sdf, String date) {
        try {
            sdf.parse(date);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    static boolean equiv_strings(String s1, String s2) {
        if (!is_double(s1.replaceAll("\\$", "").replaceAll(",", "")) || !is_double(s2.replaceAll("\\$", "").replaceAll(",", ""))) {
            return false;
        }
        return Double.parseDouble(s1.replaceAll("\\$", "").replaceAll(",", "")) == Double.parseDouble(s2.replaceAll("\\$", "").replaceAll(",", ""));
    }

    static String add(String s1, String s2) {
        if (is_double(s1.replaceAll("\\$", "").replaceAll(",", "")) && is_double(s2.replaceAll("\\$", "").replaceAll(",", ""))) {
            return finance(Double.parseDouble(s1.replaceAll("\\$", "").replaceAll(",", ""))
                    + Double.parseDouble(s2.replaceAll("\\$", "").replaceAll(",", "")));
        } else if (is_double(s1.replaceAll("\\$", "").replaceAll(",", ""))) {
            return s1;
        } else if (is_double(s2.replaceAll("\\$", "").replaceAll(",", ""))) {
            return s2;
        }
        return "$0.00";
    }

    static String finance(String s) {
        if (is_double(s)) {
            return finance(Double.parseDouble(s.replaceAll("\\$", "").replaceAll(",", "")));
        } else {
            return "$0.00";
        }
    }

    static String finance(double d) {
        String s = "" + d;
        for (int j = 0; j < s.length(); j++) {
            if (s.charAt(j) == '.') {
                if (s.length() == j + 2) {
                    s += "0";
                }
                if (j >= 7) {
                    s = "$" + s.substring(0, j - 6) + "," + s.substring(j - 6, j - 3) + "," + s.substring(j - 3, s.length());
                } else if (j >= 4) {
                    s = "$" + s.substring(0, j - 3) + "," + s.substring(j - 3, s.length());
                } else {
                    s = "$" + s;
                }
                break;
            }
        }
        return s;
    }

    static void pull_info(File file1, File file2, String column_name) {
        try {
            BufferedReader br1 = new BufferedReader(new FileReader(file1));
            BufferedReader br2 = new BufferedReader(new FileReader(file2));
            String[] header1 = (br1.readLine() + " ").split(",");
            header1[header1.length - 1] = header1[header1.length - 1].substring(0, header1[header1.length - 1].length() - 1);
            String[] header2 = (br2.readLine() + " ").split(",");
            header2[header2.length - 1] = header2[header2.length - 1].substring(0, header2[header2.length - 1].length() - 1);
            int i1 = -1, i2 = -1;
            for (int i = 0; i < header1.length; i++) {
                if (header1[i].compareTo(column_name) == 0) {
                    i1 = i;
                }
            }
            for (int i = 0; i < header2.length; i++) {
                if (header2[i].compareTo(column_name) == 0) {
                    i2 = i;
                }
            }
            if (i1 == -1) {
                JOptionPane.showMessageDialog(null, "File " + file1.getName() + " does not have a column labeled \"" + column_name + "\".");
            }
            if (i2 == -1) {
                JOptionPane.showMessageDialog(null, "File  " + file2.getName() + " does not have a column labeled \"" + column_name + "\".");
            }
            if (i1 == -1 || i2 == -1) {
                return;
            }
            format_file(file1, true);
            format_file(file2, true);
            br1 = new BufferedReader(new FileReader(file1));
            br2 = new BufferedReader(new FileReader(file2));
            header1 = (br1.readLine() + " ").split(",");
            header1[header1.length - 1] = header1[header1.length - 1].substring(0, header1[header1.length - 1].length() - 1);
            header2 = (br2.readLine() + " ").split(",");
            header2[header2.length - 1] = header2[header2.length - 1].substring(0, header2[header2.length - 1].length() - 1);
            i1 = -1;
            i2 = -1;
            for (int i = 0; i < header1.length; i++) {
                if (header1[i].compareTo(column_name) == 0) {
                    i1 = i;
                }
            }
            for (int i = 0; i < header2.length; i++) {
                if (header2[i].compareTo(column_name) == 0) {
                    i2 = i;
                }
            }
            String[] line1, line2;
            String test;
            String[][] equiv_names = load_names("equiv_names.data");
            String[][] diff_names = load_names("diff_names.data");
            String[][] new_file = new String[100000][header1.length];
            int index = 0;
            while (true) {
                test = br1.readLine();
                if (test == null) {
                    break;
                }
                line1 = read_line(header1.length, test);
                while (true) {
                    test = br2.readLine();
                    if (test == null) {
                        break;
                    }
                    line2 = read_line(header2.length, test);
                    if (are_duplicates(line1, line2, equiv_names, diff_names) == 1) {
                        if (line2[i2].length() > line1[i1].length()) {
                            line1[i1] = line2[i2];
                        }
                        break;
                    }
                }
                new_file[index] = line1;
                index++;
            }
            String timeStamp = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(Calendar.getInstance().getTime());
            create_file("Pulled_Info-" + timeStamp + ".xls", header1, new_file, index);
            JOptionPane.showMessageDialog(null, "The following file: " + print_file_name(file1) +///todo just a hook
                    " has updated records in the \"" + column_name
                    + "\" column. This new file was successfully created");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }

    public static Workbook getWorkBook(File path) { // todo make more user friendly
        try {
            return Workbook.getWorkbook(path);
        } catch (IOException ex) {
            throwError(ex, "There was a IOError with a file that you selected");
            return null;
        } catch (BiffException ex) {
            throwError(ex, "There was a BiffError with a file that you selected");
            return null;
        }
    }

    public static XSSFWorkbook NewWorkBook(File path) { // todo make more user friendly
        return new XSSFWorkbook();
    }

    public static XSSFSheet NewWorksheet(XSSFWorkbook workbook, String name) {
        return workbook.createSheet(name);
    }

    public static Sheet getWorksheet(File path) {
        Workbook workbook = getWorkBook(path);
        return workbook.getSheet(0);
    }

    public static Hashtable<String, String> dictionaryMaker(ArrayList<Pair<String, String>> pairs) {
        Hashtable<String, String> dictionary = new Hashtable<>();
        for (Pair<String, String> pair : pairs) {
            dictionary.put(pair.getKey(), pair.getValue());
        }
        return dictionary;
    }

    public static ArrayList<Pair<String, String>> fileLineReader(File file, char seperator) {//todo change name
        ArrayList<String[]> DictionaryList = new ArrayList<>(2);
        ArrayList<Pair<String, String>> pairs = new ArrayList<>();
        String currentReadLine;
        try (FileReader fr = new FileReader(file)) {
            try (BufferedReader br = new BufferedReader(fr)) {
                while ((currentReadLine = br.readLine()) != null) {
                    pairs.add(LineSplitter(currentReadLine, ','));
                }
            }
        } catch (Exception e) {
        }
        return pairListNullFilter(pairs);
    }

    public static ArrayList<Pair<String, String>> AbrFileLineReader(File file, char seperator, String[] addIns) {//todo change name
        ArrayList<Pair<String, String>> pairs = new ArrayList<>();
        String currentReadLine;

        try (FileReader fr = new FileReader(file)) {
            try (BufferedReader br = new BufferedReader(fr)) {
                while ((currentReadLine = br.readLine()) != null) {
                    pairs.addAll(AddIns(LineSplitter(currentReadLine, ','), addIns));
                }
            }
        } catch (Exception e) {
        }
        return pairListNullFilter(pairs);
    }

    public static Pair<String, String> LineSplitter(String string, char seperator) {
        String[] splitString = string.split((seperator + ""));
        if (splitString.length != TwoSplitsExpected) {
            return null;
        }
        return new Pair<>(splitString[0], splitString[1]);
    }

    public static ArrayList<Pair<String, String>> pairListNullFilter(ArrayList<Pair<String, String>> pairList) {
        ArrayList<Pair<String, String>> pairs = new ArrayList<>();
        for (int i = 0; i < pairList.size(); i++) {
            if (pairList.get(i) != null && !pairList.get(i).getKey().trim().isEmpty() && !pairList.get(i).getValue().trim().isEmpty()) {
                pairs.add(pairList.get(i));
            }
        }
        return pairs;
    }

    public static void throwError(Exception ex, String message) {// todo contain mesaage within exception
        JOptionPane.showMessageDialog(null, message);
        Logger.getLogger(ChristieProgram.class.getName()).log(Level.SEVERE, null, ex);
    }

    private static Collection<? extends Pair<String, String>> AddIns(Pair<String, String> pair, String[] addIns) {// todo check this out

        Collection<Pair<String, String>> colection = new ArrayList<>();
        colection.add(pair);
        if (addIns.length == 0) {
            return colection;
        }
        for (String addIn : addIns) {
            colection.add(new Pair(pair.getKey() + addIn, pair.getValue()));
        }
        return colection;
    }

    public static String StreetAbreviatorFix(String mod_street) {

        if (road_equiv == null) {
            String[] add_Ins = {",", "."};
            road_equiv = dictionaryMaker(AbrFileLineReader(new File("Street_Abbreviation.data"), ',', add_Ins));
        }
        String[] modStreet = mod_street.split(" ");
        for (int i = 0; i < modStreet.length; i++) {
            if (road_equiv.containsKey(modStreet[i].trim())) {
                modStreet[i] = road_equiv.get(modStreet[i].trim());
            }
        }
        return CondenseToString(modStreet);
    }

    public static String CondenseToString(String[] stringArray) {
        String string = "";
        for (String str : stringArray) {
            if (str != null) {
                string += " " + str.trim();// removesall white spaces
            }
        }
        return string.trim();
    }

    private static boolean empty(String[] strings) {
        boolean empty = true;
        for (String string : strings) {
            if (string != null && !string.trim().isEmpty() && integerParser(string) != 0) {
                empty = false;
            }
        }
        return empty;
    }

    public static int integerParser(String string) {
        
        try {
            Double d = Double.parseDouble(string.replace("$", "").replace(",", "").trim());
            return d.intValue();
        } catch (Exception e) {
            return -1;
        }
    }

    public static double DoubleParser(String string) {
        try{
        string = string.trim().replace("$", "").replace(",", "");
        return Double.parseDouble(string);
        }catch(NumberFormatException e){
           JOptionPane.showMessageDialog(null, e);
           throw e;
        }

    }

    public static void cellSetter(XSSFCell cell, String string) {
        try {
            if (integerParser(string) != -1) {
                cell.setCellValue(DoubleParser(string));
            } else {
                cell.setCellValue(new XSSFRichTextString(string));
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage()+" Line 1526");
           Logger.getLogger(ChristieProgram.class.getName()).log(Level.SEVERE, null, e);
        }
    }
}
